package com.jy.api.service.impl;

import com.jy.api.dao.ProductTypeDayCountDao;
import com.jy.api.dao.StoreDao;
import com.jy.api.domain.ProductTypeDayCount;
import com.jy.api.domain.Store;
import com.jy.api.service.TableService;
import com.jy.api.timer.ScheduledTask;
import com.jy.api.util.JodaUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.StringRedisTemplate;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;

@Slf4j
@Service
public class TableServiceImpl implements TableService {
    /**
     * 需要归集的表，有强依赖的表，要放在前面执行，否则删除数据不会成功
     */
    private static final String[] tables = new String[]{"order_item", "order", "payment", "stock_change_transaction", "call_service", "product_transaction", "stock_check_transaction", "store_num_count"};
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private StringRedisTemplate stringRedisTemplate;
    @Autowired
    private StoreDao storeDao;
    @Autowired
    private ProductTypeDayCountDao productTypeDayCountDao;

    @Override
    public List<String> backup() {
        List<String> sqls = new ArrayList<>();
        for (String table : tables) {
            final String tableKey = String.format("backup(%s)", table);
            final String value = stringRedisTemplate.opsForValue().get(tableKey);
            if (null != value) {
                log.info(String.format("表%s无需继续归集数据了...", value));
                continue;
            }

            String firstOrderSQL = String.format("SELECT `add_time` FROM `%s` ORDER BY `add_time` ASC LIMIT 1", table);
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(firstOrderSQL);
            if (!maps.isEmpty()) {
                Map<String, Object> map = maps.get(0);
                Timestamp addTime = (Timestamp) map.get("add_time");
                if (null != addTime) {
                    long time = addTime.getTime() + 8 * 60 * 60 * 1000;//服务器时区加8小时，搞不懂为啥不用中国时区

                    //大于120天的自动归集成老数据:10368000
                    if ((JodaUtils.getTimestamp() - (time / 1000)) > ScheduledTask.TRANS_DAY * 24 * 60 * 60) {
                        String yyyyMM = JodaUtils.timeLongToString(time, "yyyyMM");
                        String yyyyMMdd = JodaUtils.timeLongToString(time, "yyyy-MM-dd");
                        log.info(addTime.toString());
                        log.info(yyyyMM);
                        log.info(yyyyMMdd);

                        final List<String> strings = tableMove(table, yyyyMM, yyyyMMdd);
                        sqls.addAll(strings);
                    } else {
                        stringRedisTemplate.opsForValue().set(tableKey, table);
                        stringRedisTemplate.expire(tableKey, 5, TimeUnit.HOURS);
                        //jdbcTemplate.execute(String.format("optimize TABLE `%s`", table));
                    }
                }
            }
        }
        return sqls;
    }

    @Override
    public String productTypeDayCount(String yyyyMMdd) {
        StringBuilder buffer = new StringBuilder();
        List<Store> stores = storeDao.findListByEnabled(true);
        buffer.append(stores.size()).append("家门店、");
        AtomicInteger amount = new AtomicInteger();
        BeanPropertyRowMapper itemMapper = new BeanPropertyRowMapper<>(ProductTypeDayCount.class);
        itemMapper.setPrimitivesDefaultedForNullValue(true);
        stores.forEach(store -> {
            List<ProductTypeDayCount> dayCounts = jdbcTemplate.query("SELECT product.`parent_type_name` AS typeName, \n" +
                    "       ifnull(SUM(sales.amount), 0) as amount,\n" +
                    "       ifnull(SUM(sales.qty), 0) AS qty\n" +
                    "  FROM(\n" +
                    "SELECT *\n" +
                    "  FROM `product`\n" +
                    " WHERE `store_id`= ?) as product\n" +
                    "  LEFT JOIN(\n" +
                    "SELECT sum(`quantity`) as qty, SUM(`deal_price`) as amount, `product_id`,`add_time` \n" +
                    "  FROM `order_item`\n" +
                    " WHERE `order_id` IN(\n" +
                    "SELECT id\n" +
                    "  FROM `order`\n" +
                    " WHERE `store_id`= ?\n" +
                    "   AND `add_time`>= ?\n" +
                    "   AND `add_time`<= ?\n" +
                    "   AND `status` IN('UNFINISHED', 'FINISHED')\n" +
                    "   AND `enabled`= 1)\n" +
                    " GROUP BY  DATE_FORMAT(`add_time`,'%Y-%m-%d') ,`product_id`) AS  sales on product.id= sales.product_id\n" +
                    " WHERE qty> 0\n" +
                    " GROUP BY product.`parent_type_name`", itemMapper, store.getId(), store.getId(), yyyyMMdd + " 00:00:00", yyyyMMdd + " 23:59:59");
            if (null == dayCounts) {
                dayCounts = new ArrayList<>();
            }
            for (ProductTypeDayCount dayCount : dayCounts) {
                dayCount.setDate(JodaUtils.parse(yyyyMMdd, "yyyy-MM-dd").toDate());
                dayCount.setStoreId(store.getId());
                productTypeDayCountDao.save(dayCount);
            }
            amount.addAndGet(dayCounts.size());
        });
        buffer.append("归集").append(amount.get()).append("数据");
        return buffer.toString();
    }

    private List<String> tableMove(String table, String month, String day) {
        log.info(String.format("表数据备份 开始 %s %s", table, JodaUtils.timeLongToString()));
        jdbcTemplate.execute("SET FOREIGN_KEY_CHECKS = 0");
        List<String> sqls = new ArrayList<>();
        try {
            log.info(String.format("表%s备份创建", table));
            String createMonthTable = String.format("CREATE TABLE IF NOT EXISTS `%s_%s` like `%s`", table, month, table);
            log.info(createMonthTable);
            jdbcTemplate.execute(createMonthTable);
            sqls.add(createMonthTable);
        } catch (Exception e) {
            e.printStackTrace();
            log.error(String.format("表%s备份创建错误", table), e);
        }

        try {
            String targetTableName = String.format("%s_%s", table, month);
            log.info(String.format("表%s与备份表%s结构对比检查", table, targetTableName));
            HashMap<String, Map<String, Object>> sourceTableMap = new HashMap<>();
            HashMap<String, Map<String, Object>> targetTableMap = new HashMap<>();
            final List<Map<String, Object>> sourceFields = jdbcTemplate.queryForList(String.format("desc `%s`", table));
            for (Map<String, Object> map : sourceFields) {
                sourceTableMap.put(map.get("Field").toString(), map);
            }
            final List<Map<String, Object>> targetFields = jdbcTemplate.queryForList(String.format("desc `%s`", targetTableName));
            for (Map<String, Object> map : targetFields) {
                targetTableMap.put(map.get("Field").toString(), map);
                final String fieldName = map.get("Field").toString();
                final String fieldType = map.get("Type").toString();
                final Map<String, Object> sourceFieldMap = sourceTableMap.get(fieldName);
                if (sourceTableMap.containsKey(fieldName)) {
                    if (!fieldType.equals(sourceFieldMap.get("Type"))) {
                        String modifyColumnSQL;
                        if (sourceFieldMap.containsKey("Default")) {
                            modifyColumnSQL = String.format("alter table `%s` modify column %s %s default %s", targetTableName, fieldName, sourceFieldMap.get("Type"), sourceFieldMap.get("Default"));
                        } else {
                            modifyColumnSQL = String.format("alter table `%s` modify column %s %s", targetTableName, fieldName, sourceFieldMap.get("Type"));
                        }
                        log.info(modifyColumnSQL);
                        sqls.add(modifyColumnSQL);
                        jdbcTemplate.execute(modifyColumnSQL);
                    }
                } else {
                    final String dropColumnSQL = String.format("alter table `%s` drop column %s", targetTableName, fieldName);
                    log.info(dropColumnSQL);
                    sqls.add(dropColumnSQL);
                    jdbcTemplate.execute(dropColumnSQL);
                }
            }
            for (Map<String, Object> map : sourceFields) {
                final String fieldName = map.get("Field").toString();
                if (!targetTableMap.containsKey(fieldName)) {
                    String addColumnSQL;
                    if (map.containsKey("Default")) {
                        addColumnSQL = String.format("alter table `%s` add column %s %s default %s", targetTableName, fieldName, map.get("Type"), map.get("Default"));
                    } else {
                        addColumnSQL = String.format("alter table `%s` add column %s %s", targetTableName, fieldName, map.get("Type"));
                    }
                    log.info(addColumnSQL);
                    sqls.add(addColumnSQL);
                    jdbcTemplate.execute(addColumnSQL);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error(String.format("表%s结构检查错误", table), e);
        }

        try {
            log.info(String.format("表%s插入数据", table));
            String insertIntoMonthTable = String.format("INSERT INTO `%s_%s` SELECT * FROM `%s` WHERE `add_time` <= '%s 23:59:59'", table, month, table, day);
            log.info(insertIntoMonthTable);
            jdbcTemplate.execute(insertIntoMonthTable);
            sqls.add(insertIntoMonthTable);
        } catch (Exception e) {
            e.printStackTrace();
            log.error(String.format("表%s数据转移错误", table), e);
        }
        try {
            log.info(String.format("表%s删除数据", table));
            String delete = String.format("DELETE FROM `%s` WHERE `add_time` <= '%s 23:59:59'", table, day);
            log.info(delete);
            jdbcTemplate.execute(delete);
            sqls.add(delete);
        } catch (Exception e) {
            e.printStackTrace();
            log.error(String.format("表%s数据删除错误", table), e);
        }
        jdbcTemplate.execute("SET FOREIGN_KEY_CHECKS = 1");
        log.info(String.format("表数据备份 结束 %s %s", table, JodaUtils.timeLongToString()));
        return sqls;
    }
}
